package com.szrt.maven_jdbc_demo.maven_jdbc_demo.utils;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.junit.Test;

import com.szrt.maven_jdbc_demo.maven_jdbc_demo.entity.StudentEntity;


public class DBUtil {
	
	/**
	 * 获得数据库连接
	 * @return
	 */
	public static Connection getConnection() {
		Connection connection = null;
		try {
			//加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/db1609";
			String user = "root";
			String password = "123456";
			//com.mysql.jdbc.Driver.class
			//连接数据库
			connection = DriverManager.getConnection(url, user, password);
			System.out.println("yes");
		} catch (Exception e) {
			System.err.println("no");
		}
		return connection;
	}
	/**
	 * 关闭数据库连接
	 * @param connection
	 */
	public static  void closeConnection(Connection connection){
		if(connection!=null){
			try {
				connection.close();
				System.out.println("关闭成功");
			} catch (SQLException e) {
				System.out.println("关闭失败");
			}
		}
		
	}
	@Test
	public void testName() throws Exception {
		//获取数据库的连接对象
		Connection connection = getConnection();
		String sql = " select * from crm_student_t ";
		PreparedStatement ps = connection.prepareStatement(sql);
		//执行sql查询  并返回结果集
		ResultSet re = ps.executeQuery();
		List<StudentEntity> list = new ArrayList<StudentEntity>();
		//遍历结果集
		while(re.next()){
			String studentName = re.getString("STUDENT_NAME");
			String id = re.getString("ID");
			String age = re.getString("AGE");
			String sex = re.getString("SEX");
			//将数据库中得到的数据 进行打包
			StudentEntity stu = new StudentEntity();
			stu.setAge(Integer.parseInt(age));
			stu.setId(Integer.parseInt(id));
			stu.setSex(sex);
			stu.setStudentName(studentName);
			//将每一条打包好的数据 存进list集合中
			list.add(stu);
		}
		for(StudentEntity stu:list){
			System.out.println(stu);
		}
		closeConnection(connection);
	}
	
}
